﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spDBA_get_managed_server_list]
#-- Purpose:		Get a list of managed servers to query for updated information
#--	Last Update:	05/15/2012
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spDBA_get_managed_server_list]
AS

SET NOCOUNT ON

--- Return a list of servers
SELECT		srv_type_ro,
			srv_description, 
			srv_server_name, 
			srv_product_version,
			CAST(CASE WHEN should_hide = 1 THEN 0 ELSE access_granted END as bit) is_visible
FROM		(
			SELECT		srv.srv_type_ro,
						srv.srv_description, 
						srv.srv_server_name, 
						srv.srv_product_version,
						CAST(MAX(CAST(ISNULL(rst.access_granted, 0) as int)) as bit) access_granted,
						CAST(MAX(CAST(ISNULL(rst.rst_should_hide, 0) as int)) as bit) should_hide
			FROM		dbo.tblServer srv
			LEFT JOIN	(
						SELECT		rst_server_name,
									(CASE	WHEN IS_MEMBER(rst_username) = 1 OR rst_username = '*'
											THEN 1 ELSE 0 END) access_granted,
									(CASE	WHEN rst_username = SUSER_NAME() 
											THEN rst_should_hide ELSE 0 END) rst_should_hide
						FROM		dbo.tblServerRestoreUser
						) rst ON rst.rst_server_name = srv.srv_server_name
							OR rst.rst_server_name = '*'
			WHERE		srv.srv_is_active = 1
						AND srv.srv_is_disabled = 0
						AND srv.srv_is_vendor_managed_ro = 0
			GROUP BY	srv.srv_type_ro,
						srv.srv_description, 
						srv.srv_server_name, 
						srv.srv_product_version
			) t1
WHERE		CAST(CASE WHEN should_hide = 1 THEN 0 ELSE access_granted END as bit) = 1
ORDER BY	srv_type_ro,
			srv_description

SET NOCOUNT OFF